// 导入模块
const mysql = require('mysql2/promise');
const dataBase = require('../config/index.js');

// 创建连接池
const pool = mysql.createPool({
    host: dataBase.HOST,
    user: dataBase.USERNAME,
    password: dataBase.PASSWORD,
    database: dataBase.DATABASE,
    port: dataBase.PORT
});

// mysql的连接
const allServices = {
    async query(sql, values) {
        try {
            const conn = await pool.getConnection();  // 等待连接池连接
            const [rows, fields] = await conn.query(sql, values); // 执行sql语句
            pool.releaseConnection(conn); // 释放连接
            return Promise.resolve(rows);
        } catch (error) {
            return Promise.reject(error);
        }

    }
}

// 登陆功能
const userLogin = (username, password) => {
    let _sql = `SELECT * FROM users WHERE username='${username}' AND password='${password}';`
    return allServices.query(_sql)
}

// 注册功能
const userRegister = (username, password) => {
    let _sql = `INSERT INTO users (username, password)
                SELECT * FROM (SELECT '${username}' AS username, '${password}' AS password) AS temp
                WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = '${username}')
                LIMIT 1;
                `
    return allServices.query(_sql)
}

// 修改用户目标体重功能
const changeTargetWeight = (targetWeight, id) => {
    let _sql = `UPDATE target
                SET targetWeight = ${targetWeight}
                WHERE user_id = ${id};
                `
    return allServices.query(_sql)
}

// 修改用户当前体重功能
const changeNowWeight = (user_id, nowWeight, time) => {
    let _sql = `
                INSERT INTO weight (user_id, nowWeight, create_time)
                VALUES (${user_id},${nowWeight}, ${time})
                ON DUPLICATE KEY UPDATE
                nowWeight = VALUES(nowWeight);
                `
    return allServices.query(_sql)
}

// 获取用户最近7天的体重数据
const getWeightData = (user_id, time) => {
    let _sql = `SELECT *
                FROM weight
                WHERE user_id = ${user_id} AND  create_time <= ${time}
                ORDER BY create_time DESC
                LIMIT 7;
`
    return allServices.query(_sql)
}

//获取用户的目标体重、目标摄入和目标消耗
const getTargetData = (user_id) => {
    let _sql = `SELECT * FROM target WHERE user_id = ${user_id}`
    return allServices.query(_sql)
}

//写入用户的目标体重、目标摄入和目标消耗
const writeTargetData = (targetWeight, targetIntake, targetConsume, user_id) => {
    let _sql = `INSERT INTO target (targetWeight, targetIntake, targetConsume, user_id)
                VALUES (${targetWeight}, ${targetIntake}, ${targetConsume}, ${user_id})
                `
    return allServices.query(_sql);
}

// 获取用户每日的餐食
const getDietData = (user_id, time) => {
    let _sql = `SELECT * from diet where user_id = ${user_id} AND  create_time = ${time} ORDER BY time`
    return allServices.query(_sql)
}

// 修改目标摄入热量
const changeTargetIntake = (targetIntake, id) => {
    let _sql = `UPDATE target
                SET targetIntake = ${targetIntake}
                WHERE user_id = ${id};`
    return allServices.query(_sql)
}

// 修改目标消耗热量
const changeTargetConsume = (targetConsume, id) => {
    let _sql = `UPDATE target
                SET targetConsume = ${targetConsume}
                WHERE user_id = ${id};`
    return allServices.query(_sql)
}

// 写入一条食物数据
const writeFoodData = (user_id, component, time, create_time) => {
    // let _sql = `INSERT INTO diet (create_time, user_id, mealType, mealName, calorie, protein, carbon, fat, time)
    //             VALUES (${create_time}, ${user_id}, ${component.mealType}, ${component.mealName}, ${component.calories}, ${component.protein}, ${component.carbohydrates}, ${component.fat}, ${time})
    //             `
    // return allServices.query(_sql);
    let _sql = `INSERT INTO diet (create_time, user_id, mealType, mealName, calorie, protein, carbon, fat, time)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

    let params = [create_time, user_id, component.mealType, component.mealName, component.calories, component.protein, component.carbohydrates, component.fat, time];

    return allServices.query(_sql, params);
}

// 删除一条食物数据
const deleteFoodData = (id) => {
    let _sql = `DELETE FROM diet WHERE id = ${id}`
    return allServices.query(_sql)
}

// 请求本周的运动数据
const getSportData = (user_id, time) => {
    let _sql = `SELECT *
                FROM sport
                WHERE user_id = ${user_id}
                AND create_time >= UNIX_TIMESTAMP(DATE_SUB(FROM_UNIXTIME(${Number(time)} / 1000), INTERVAL WEEKDAY(FROM_UNIXTIME(${Number(time)} / 1000)) DAY)) * 1000
                AND create_time <= ${Number(time)};
                `
    return allServices.query(_sql)
}

// 删除一条运动数据
const deleteSportData = (id) => {
    let _sql = `DELETE FROM sport WHERE id = ${id}`
    return allServices.query(_sql)
}

// 写入一条运动数据
const writeSportData = (user_id, timeRange, minute, sportType, calorie, create_time) => {
    let _sql = `INSERT INTO sport (user_id, timeRange, minute, sportType, calorie, create_time)
                VALUES (?, ?,?,?,?,?)`;
    let params = [user_id, timeRange, minute, sportType, calorie, create_time]
    console.log(params);
    return allServices.query(_sql, params)
}


module.exports = {
    userLogin,
    userRegister,
    changeTargetWeight,
    changeNowWeight,
    getWeightData,
    getTargetData,
    writeTargetData,
    getDietData,
    changeTargetIntake,
    changeTargetConsume,
    writeFoodData,
    deleteFoodData,
    getSportData,
    deleteSportData,
    writeSportData
}